﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using ChinaDynastyTours.Models;
using ChinaDynastyTours.Helpers;

namespace ChinaDynastyTours.Controls
{
    class BillCtrl
    {
        #region 获取对账单信息

        /// <summary>
        /// 根据行程ID判断是否存在地接社“对账单”数据
        /// </summary>
        /// <param name="tourGroupScheduleID"></param>
        /// <returns></returns>
        internal static bool IsExistBillData(string tourGroupScheduleID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            string Sql = string.Format(@"select BillID from fee_Bill where ExpenseID=(select expenseid from fee_TravelOrder where TourGroupScheduleID={0})", tourGroupScheduleID);
            dbCommand = db.GetSqlStringCommand(Sql);
            object obj = db.ExecuteScalar(dbCommand);
            return obj != null;
        }



        /// <summary>
        /// 获取对账单中所有地接社的信息
        /// </summary>
        /// <returns></returns>
        internal static DataTable GetProviderInfoForBill(string tourgroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            string Sql = string.Format(@"SELECT DISTINCT b.ProviderID,p.ProviderName FROM fee_Bill b,bas_Provider p WHERE b.ProviderID=p.ProviderID AND b.TourgroupID='{0}'", tourgroupId);
            dbCommand = db.GetSqlStringCommand(Sql);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        internal static decimal GetBillSum(string tourgroupId, string providerId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            string Sql = string.Format(@"SELECT SUM(Amount) FROM fee_Bill WHERE TourgroupID='{0}' AND ProviderID={1}", tourgroupId, providerId);
            dbCommand = db.GetSqlStringCommand(Sql);
            object obj = db.ExecuteScalar(dbCommand);
            return obj == null ? 0 : decimal.Parse(obj.ToString());
        }
        /// <summary>
        /// 获取对账单数据
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        internal static DataTable GetBillData(string tourgroupId, string providerID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            string Sql;

            Sql = string.Format(@"SELECT expenseid FROM fee_Expense WHERE TourgroupID='{0}' AND ProviderID={1}", tourgroupId, providerID);
            dbCommand = db.GetSqlStringCommand(Sql);
            object obj = db.ExecuteScalar(dbCommand);

            if (obj != null)
            {
                Sql = string.Format(@"SELECT *,e.Status FROM fee_Bill b,fee_expense e WHERE b.ExpenseID=e.ExpenseID AND b.ExpenseID={0} AND b.TourgroupID='{1}' AND b.ProviderID={2}  ORDER BY b.ExpenseType", obj, tourgroupId, providerID);
                dbCommand = db.GetSqlStringCommand(Sql);
                DataSet ds = db.ExecuteDataSet(dbCommand);
                if (ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
            else
                return null;
        }

        #endregion

        #region 对账单信息的增、删、改

        /// <summary>
        /// 更新内容
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        internal static int UpdateBillData(BillEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE [fee_Bill]
                                         SET [Amount]={0}, [ExpenseType]='{1}', [Remark]='{2}'
                                         WHERE BillID={3}",
                                        entity.Amount,
                                        entity.ExpenseType,
                                        entity.Remark,
                                        entity.BillID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

    }
}
